﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using MeetingMinutesManager.DB;
using System.Net.Mail;

namespace MeetingMinutesManager
{
    public class ResourceOperator
    {
        /// <summary>
        /// Read the team for the user.
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static String ReadTeam(String userName)
        {
            String strQuery = "select team from usr where usr_name = '" + userName + "'";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtTeam = dbOperator.Read(strQuery);

            String team = String.Empty;
            foreach (DataRow row in dtTeam.Rows)
            {
                if (row["team"] != DBNull.Value)
                {
                    team = row["team"].ToString();
                }
            }

            return team;
        }

        public static String ReadResourceName(String userID)
        {
            String strQuery = "select usr_name from usr where id = " + userID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtResults = dbOperator.Read(strQuery);

            String returnString = String.Empty;
            foreach (DataRow row in dtResults.Rows)
            {
                if (row["usr_name"] != DBNull.Value)
                {
                    returnString = row["usr_name"].ToString();
                }
            }

            return returnString;
        }

        public static String ReadManager(String userID)
        {
            String strQuery = "select mgr_id from usr where id = " + userID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtManager = dbOperator.Read(strQuery);

            String manager = String.Empty;
            foreach (DataRow row in dtManager.Rows)
            {
                if (row["mgr_id"] != DBNull.Value)
                {
                    manager = row["mgr_id"].ToString();
                }
            }

            return manager;
        }
        public static String ReadManagerEmailByEmail(String email)
        {
            String strQuery = "select email from usr where id = (select mgr_id from usr where email = '" + email + "')";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtManager = dbOperator.Read(strQuery);

            String managerEmail = String.Empty;
            foreach (DataRow row in dtManager.Rows)
            {
                if (row["email"] != DBNull.Value)
                {
                    managerEmail = row["email"].ToString();
                }
            }

            return managerEmail;
        }

        /// <summary>
        /// Read the user ID
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static String ReadResourceID(String userName)
        {
            String strQuery = "select id from usr where usr_name = '" + userName + "'";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtTeam = dbOperator.Read(strQuery);

            String team = String.Empty;
            foreach (DataRow row in dtTeam.Rows)
            {
                if (row["id"] != DBNull.Value)
                {
                    team = row["id"].ToString();
                }
            }

            return team;
        }
        /// <summary>
        /// Read all resources
        /// </summary>
        /// <returns>String[]</returns>
        public static String[] ReadResources()
        {
            return ReadResources(String.Empty);
        }

        /// <summary>
        /// Read all resources based on team. If team is empty, return all.
        /// </summary>
        /// <param name="team">Example, 'NF1', 'NF2', 'OPTM'</param>
        /// <returns></returns>
        public static String[] ReadResources(String team)
        {
            // Collect Resources
            String strQuery = "select usr_name from usr order by usr_name";
            if (!team.Equals(String.Empty))
            {
                strQuery = "select usr_name from usr where team in (" + team + ") order by usr_name";
            }

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtResources = dbOperator.Read(strQuery);

            String[] resources = new String[dtResources.Rows.Count];
            int i = 0;
            foreach (DataRow row in dtResources.Rows)
            {
                String name = row["usr_name"].ToString();

                resources[i] = name;
                i++;
            }

            return resources;
        }

        /// <summary>
        /// Read all resources.
        /// </summary>
        /// <returns>String, i.e. 'rwei','vpang'</returns>
        public static String ReadResouceList()
        {
            return ReadResouceList(String.Empty);
        }
        
        /// <summary>
        /// Read resource names into a list
        /// </summary>
        /// <returns></returns>
        public static String ReadResouceList(String team)
        {
            // Collect resouces
            String[] resourcesList = ReadResources(team);

            // Convert the array into a string
            string resourceList = String.Empty;
            for (int i = 0; i < resourcesList.Length; i++)
            {
                if (resourcesList[i] == null)
                {
                    continue;
                }

                if (resourceList.Equals(String.Empty))
                {
                    resourceList = "'" + resourcesList[i] + "'";
                }
                else
                {
                    resourceList += ",'" + resourcesList[i] + "'";
                }
            }

            return resourceList;
        }

        /// <summary>
        /// Read the resources for NF1
        /// </summary>
        /// <returns>String, i.e. 'vpang', 'rban'</returns>
        public static String ReadVincentResources()
        {
            return ReadResouceListByTeam("NF1");
        }

        /// <summary>
        /// Read the resources for NF2
        /// </summary>
        /// <returns>String, i.e. 'kdai', 'pyang'</returns>
        public static String ReadKimResources()
        {
            return ReadResouceListByTeam("NF2");
        }

        /// <summary>
        /// Read resources based on team.
        /// </summary>
        /// <param name="team">String</param>
        /// <returns>String, i.e. 'rwei', 'mji'</returns>
        private static String ReadResouceListByTeam(String team)
        {
            string resourceList = String.Empty;

            // Collect Resources based on team
            String strReadResources = "select usr_name name from usr where team = '" + team + "' order by usr_name";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtResources = localOperator.Read(strReadResources);

            foreach (DataRow row in dtResources.Rows)
            {
                if (row["name"] != null && !row["name"].ToString().Equals(String.Empty))
                {
                    if (resourceList.Equals(String.Empty))
                    {
                        resourceList = "'" + row["name"].ToString().Trim() + "'";
                    }
                    else
                    {
                        resourceList += ",'" + row["name"].ToString().Trim() + "'";
                    }
                }
            }

            return resourceList;
        }

        public static MailAddress GetEmailAddress(String owner)
        {
            String strQueryAgenda = "select top 1 email from usr where usr_name = '" + owner + "'";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtEmail = localOperator.Read(strQueryAgenda);

            foreach (DataRow row in dtEmail.Rows)
            {
                String addressString = row["email"].ToString().Trim();
                MailAddress emailAddress = new MailAddress(addressString);

                return emailAddress;
            }

            return new MailAddress("rain.wei@bleum.com");
        }

        /// <summary>
        /// Return true if the user name and password exists
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public static bool AuthenticateUser(String userName, String password)
        {
            bool isAuthenticated = false;

            String strQueryExits = "select count(id) exist from usr where is_approved = 1 and usr_name = '" + userName + "' and password = '" + password + "'";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtExists = localOperator.Read(strQueryExits);

            foreach (DataRow row in dtExists.Rows)
            {
                if (row["exist"] != DBNull.Value)
                {
                    if (Convert.ToInt32(row["exist"]) >= 1)
                    {
                        isAuthenticated = true;
                    }
                }
            }

            return isAuthenticated;
        }

        /// <summary>
        /// Return true if the user is a lead
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static bool IsUserAManager(String userName)
        {
            bool isLead = false;

            String strQueryExits = "select count(usr_name) exist from usr where is_approved = 1 and usr.role >= 100 and usr_name = '" + userName + "'";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtExists = localOperator.Read(strQueryExits);

            foreach (DataRow row in dtExists.Rows)
            {
                if (row["exist"] != DBNull.Value)
                {
                    if (Convert.ToInt32(row["exist"]) >= 1)
                    {
                        isLead = true;
                    }
                }
            }

            return isLead;
        }

        public static bool IsUserExists(String userName)
        {
            bool isExists = false;

            String strQueryExits = "select count(usr_name) exist from usr where usr_name = '" + userName + "'";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtExists = localOperator.Read(strQueryExits);

            foreach (DataRow row in dtExists.Rows)
            {
                if (row["exist"] != DBNull.Value)
                {
                    if (Convert.ToInt32(row["exist"]) >= 1)
                    {
                        isExists = true;
                    }
                }
            }

            return isExists;
        }

        public static String ReadUserRole(String userName)
        {
            String role = String.Empty;

            String strQueryExits = "select role from usr where usr_name = '" + userName + "'";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dt = localOperator.Read(strQueryExits);

            foreach (DataRow row in dt.Rows)
            {
                if (row["role"] != DBNull.Value)
                {
                    role = row["role"].ToString().Trim();
                }
            }

            return role;
        }

        public static MailAddressCollection ReadAllEmails()
        {
            MailAddressCollection sendTos = new MailAddressCollection();
            String strQuery = "select distinct email from usr where email is not null and email <> ''";

            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtAlLEmails = localOperator.Read(strQuery);

            foreach (DataRow row in dtAlLEmails.Rows)
            {
                if (row["email"] != DBNull.Value)
                {
                    MailAddress emailAddress = new MailAddress(row["email"].ToString().Trim());
                    sendTos.Add(emailAddress);
                }
            }

            return sendTos;
        }

        public static void AddNewUser(String userName, String password, String email, String team, String role, String manager)
        {
            String strInsert = "insert into usr (usr_name, password, team, email, role ";

            if (manager.Equals(String.Empty))
            {
                strInsert += ")";
            }
            else
            {
                strInsert += ", mgr_id)";
            }

            strInsert += " values ('" + userName + "','" + password + "'," + team + ",'" + email + "'," + role;

            if (manager.Equals(String.Empty))
            {
                strInsert += ")";
            }
            else
            {
                strInsert += ", " + manager + ")";
            }

            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            localOperator.Update(strInsert);
        }

        public static void SendNewUserApprovalRequest(String userName)
        {
            String subject = "New User Approval Request";
            String body = "New User <" + userName + "> Waiting for Approval.";

            MailAddress sender = GetEmailAddress(userName);
            MailAddress admin = new MailAddress("rain.wei@bleum.com");

            MailAddressCollection recieves = new MailAddressCollection();
            recieves.Add(sender);
            recieves.Add(admin);

            EmailOperator.Send(subject, body, recieves, sender, false, null);
        }

        public static void SendNewUserApproval(String userName)
        {
            String subject = "New User Approval Request - Approved";
            String body = "New User <" + userName + "> Approved.";

            MailAddress sender = GetEmailAddress(userName);
            MailAddress admin = new MailAddress("rain.wei@bleum.com");

            MailAddressCollection recieves = new MailAddressCollection();
            recieves.Add(sender);
            recieves.Add(admin);

            EmailOperator.Send(subject, body, recieves, admin, false, null);
        }

        public static void ApproveUser(String userID)
        {
            String strUpdate = "update usr set is_approved = 1 where id = " + userID;
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            localOperator.Update(strUpdate);
        }
    }
}
